"""
Case Type   : Timescaledb功能测试
Case Name   : 通过系统表、元命令查看数据块chunk信息
Create At   : 2025/05/15
Owner       : lihongji
Description :
              1.创建自定义函数
              2.使用系统表查看chunk信息
              3.通过元命令查看chunk信息
              4.清理环境
Expect      :
              1.创建自定义函数成功
              2.查看成功
              3.查看成功
              4.清理环境成功
History     :
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class Timescaledb(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbPGmode')
        self.db_user_node = Node(node='PrimaryDbPGmode')
        status = self.primary_sh.get_db_cluster_status()
        self.assertTrue("Normal" in status or "Degraded" in status)
        select_extension_cmd = f"SELECT * FROM pg_extension;"
        select_extension_result = self.primary_sh.execut_db_sql(
            select_extension_cmd)
        if "timescaledb" not in select_extension_result:
            create_extension_cmd = f"create extension timescaledb;"
            create_extension_result = (
                self.primary_sh.execut_db_sql(create_extension_cmd))
            self.assertIn(self.constant.extension_create_success,
                          create_extension_result, '创建timescaledb扩展失败')

    def test_add_dimension(self):
        text = '----step1:创建函数 expect:函数创建成功----'
        self.log.info(text)
        create_function = """
        CREATE OR REPLACE FUNCTION dimension_get_time(hypertable_id INT)
        RETURNS _timescaledb_catalog.dimension LANGUAGE SQL STABLE AS
        \$BODY\$
        SELECT * FROM _timescaledb_catalog.dimension d 
        WHERE d.hypertable_id = dimension_get_time.hypertable_id AND 
        d.interval_length IS NOT NULL
        \$BODY\$;"""
        create_result = self.primary_sh.execut_db_sql(create_function)
        self.log.info(create_function)
        self.log.info(create_result)
        self.assertEqual(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                         create_result, '执行失败' + text)

        text = '----step2: 使用系统表查看chunk信息 expect: 查看成功----'
        self.log.info(text)
        chunk = """SELECT c.id AS chunk_id, c.hypertable_id, 
        c.schema_name AS chunk_schema, c.table_name AS chunk_table, 
        ds.range_start, ds.range_end FROM _timescaledb_catalog.chunk c
        INNER JOIN _timescaledb_catalog.hypertable h 
        ON (c.hypertable_id = h.id)
        INNER JOIN dimension_get_time(h.id) time_dimension ON(true)
        INNER JOIN _timescaledb_catalog.dimension_slice ds 
        ON (ds.dimension_id = time_dimension.id)
        INNER JOIN _timescaledb_catalog.chunk_constraint cc 
        ON (cc.dimension_slice_id = ds.id AND cc.chunk_id = c.id)
        WHERE h.schema_name = 'public' AND (h.table_name = 'drop_chunk_test1' 
        OR h.table_name = 'drop_chunk_test2')
        ORDER BY c.id;"""
        chunk_result = self.primary_sh.execut_db_sql(chunk)
        self.log.info(chunk)
        self.log.info(chunk_result)
        self.assertIn('(0 rows)', chunk_result, '执行失败' + text)

        text = '----step3:通过元命令查看chunk信息 expect:查看成功----'
        self.log.info(text)
        metacommand = '\dt "_timescaledb_internal"._hyper*'
        metacommand_result = self.primary_sh.execut_db_sql(metacommand)
        self.log.info(metacommand)
        self.log.info(metacommand_result)
        self.assertIn('No matching relations found',
                      metacommand_result, '执行失败' + text)

    def tearDown(self):
        text = '-----step5:清理环境 expect:成功-----'
        self.log.info(text)
        drop_function = "drop function dimension_get_time;"
        drop_function_result = self.primary_sh.execut_db_sql(drop_function)
        self.assertEqual(self.constant.DROP_FUNCTION_SUCCESS_MSG,
                         drop_function_result, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
